﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Leiloes;

namespace DLGeneric
{
    public class DLGeneric
    {

        string connectionStringLeiloes = "Data Source=MONIZ-PORTATIL;Initial Catalog=leilao;Persist Security Info=True;User ID=sa;Password=pass";
        string connectionStringContabilidade = "Data Source=MONIZ-PORTATIL;Initial Catalog=contabilidade;Persist Security Info=True;User ID=sa;Password=pass";
       // string connectionString = "Data Source=.\MSSQLSERVERR2;Initial Catalog=Auction;Persist Security Info=True;User ID=arc;Password=arc";

        /// <summary>
        /// Estabelece ligação à base de dados para autenticar o cliente
        /// </summary>
        /// <param name="user"></param>
        /// <param name="pass"></param>
        /// <returns>id do cliente</returns>
        public string Autenticar(string user, string pass)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            string ret = "";

            try
            {
                conn = new SqlConnection(connectionStringLeiloes);
                conn.Open();
                SqlCommand cmd = new SqlCommand("Autenticar", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("user", user);
                cmd.Parameters.AddWithValue("pass", pass);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    ret = rdr["id"].ToString();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Ocorreu um erro na ligação à BD \n " + e.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (rdr.IsClosed != true) rdr.Close();
            }

            return ret;
        }

        /// <summary>
        /// Estabelece ligação à bd e devolve o saldo do cliente
        /// </summary>
        /// <param name="id"></param>
        /// <returns>Saldo do cliente</returns>
        public string VerSaldo(int id)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            string ret = "";

            try
            {
                conn = new SqlConnection(connectionStringContabilidade);
                conn.Open();
                SqlCommand cmd = new SqlCommand("verSaldo", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("id_user", id);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    ret = rdr["saldo"].ToString();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Ocorreu um erro na ligação à BD \n " + e.ToString());
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                if (rdr.IsClosed != true) rdr.Close();
            }

            return ret;
        }

        /// <summary>
        /// Estabelece ligação à bd e deposita valor na conta do cliente
        /// </summary>
        /// <param name="id"></param>
        /// <param name="valor"></param>
        /// <returns>devolve o saldo do cliente</returns>
        public double depositar(int id, double valor)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            double ret = 0.0;

            try
            {
                conn = new SqlConnection(connectionStringContabilidade);
                conn.Open();
                SqlCommand cmd = new SqlCommand("[efetuarDeposito]", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("id_user", id);
                cmd.Parameters.AddWithValue("valor", valor);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    ret = double.Parse(rdr["saldo"].ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Ocorreu um erro na ligação à BD \n " + e.ToString());
            }
            finally
            {
                if (conn != null) conn.Close();
                if (rdr.IsClosed != true) rdr.Close();
            }
            return ret;
        }

        
        public double adicionarLeiao(Leilao leilao)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            double ret = 0.0;

            try
            {
                conn = new SqlConnection(connectionStringLeiloes);
                conn.Open();
                SqlCommand cmd = new SqlCommand("[efetuarDeposito]", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("id_vendedor", leilao.vendedorID);
                cmd.Parameters.AddWithValue("titulo", leilao.nome);
                cmd.Parameters.AddWithValue("descricao", leilao.descricaoArtigo);
                cmd.Parameters.AddWithValue("valorbase", leilao.valorInicial);
                cmd.Parameters.AddWithValue("datainicio", leilao.dataInicio);
                cmd.Parameters.AddWithValue("datafim", leilao.dataFim);
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    ret = double.Parse(rdr["id"].ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Ocorreu um erro na ligação à BD \n " + e.ToString());
            }
            finally
            {
                if (conn != null) conn.Close();
                if (rdr.IsClosed != true) rdr.Close();
            }
            return ret;
        }

        /// <summary>
        /// Estabelece ligação à bd e mostra qauis os leilões ativos do cliente
        /// </summary>
        /// <param name="id"></param>
        /// <returns> leilões ativos</returns>
        public ArrayList verAtivos(int id)
        {
            SqlConnection conn = null;
            SqlDataReader rdr = null;
            ArrayList lista = new ArrayList();
            Leilao leilao = null;
            try
            {
                conn = new SqlConnection(connectionStringLeiloes);
                conn.Open();
                SqlCommand cmd = new SqlCommand("verativos", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("id", id);
                rdr = cmd.ExecuteReader();
                rdr.Read();
                lista.Add(rdr["num"].ToString());
                while (rdr.Read())
                {
                    leilao = new Leilao();
                    leilao.id = int.Parse(rdr["id"].ToString());
                    leilao.nome = rdr["nome"].ToString();
                    leilao.dataInicio = DateTime.Parse(rdr["datainicio"].ToString());
                    leilao.dataFim = DateTime.Parse(rdr["datafim"].ToString());
                    leilao.valorInicial = double.Parse(rdr["valorinicial"].ToString());
                    leilao.valorAtual = double.Parse(rdr["valoratual"].ToString());
                    leilao.ativo = rdr["ativo"].ToString();
                    lista.Add(leilao);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("ocorreu um erro na ligação à bd \n " + e.ToString());
            }
            finally
            {
                if (conn != null) conn.Close();
                if (rdr.IsClosed != true) rdr.Close();
            }
            return lista;
        }
    }
}
